<?php
require "Classes/PHPExcel.php";

/**
 使用案例
require "core/helpers/excel/demo.php";

$head = array("name" => "姓名", "staff_id" => "员工编号", "day" => "考勤日期", "work_status_msg" => "考勤状态", "jet_hour" => "时长(小时)", "jet_hour_msg" => "加班时间段");
$data = array(
array("name" => "赵新年", "staff_id" => "1111", "day" => "2018-5-5", "work_status_msg" => "加班", "jet_hour" => "2.0", "jet_hour_msg" => "18:00 - 20:00"),
array("name" => "赵名可", "staff_id" => "2222", "day" => "2018-9-1", "work_status_msg" => "早退", "jet_hour" => "0.5", "jet_hour_msg" => ""),
array("name" => "小王", "staff_id" => "3333", "day" => "2018-7-8", "work_status_msg" => "迟到", "jet_hour" => "1.02", "jet_hour_msg" => ""),
);
// 检测长数据
//$data = [
//    [
//        "a" => "", "b" => "", "b1" => "", "b2" => "", "b3" => "", "b4" => "", "b5" => "", "b6" => "", "b7" => "", "b8" => "", "b9" => "", "b10" => "", "b11" => "", "b27" => "",
//        "b13" => "", "b14" => "", "b15" => "", "b16" => "", "b17" => "", "b18" => "", "b19" => "", "b20" => "", "b21" => "", "b22" => "", "b23" => "", "b24" => "", "b25" => "", "b26" => "",
//        "b28" => "", "b29" => "", "b30" => "", "b31" => "", "b32" => "", "b33" => "", "b34" => "", "b35" => "", "b36" => "", "b37" => "", "b38" => "", "b39" => "", "b40" => "", "b41" => "",
//        "b42" => "", "b43" => "", "b44" => "", "b45" => "", "b46" => "", "b47" => "", "b48" => "", "b49" => "", "b50" => "", "b51" => "", "b52" => "", "b53" => "",
//    ],
//];
$e = new Demo();
$e->export($head, $data);

 */
class Demo
{
    const ROW = [
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "S", "Y", "Z",
    ];

    // 列头首个
    protected static $row_num = 1;
    // 当前索引到的列序号
    protected static $index = 0;
    /**
     * @param array $header
     * @param array $data
     * @param string $fileName
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception
     */
    public function export($header = [], $data = [],$fileName = "")
    {

        $obj_PHPExcel = new PHPExcel();//实例化PHPExcel类 等同于新建一个Excel表格
        $obj_Sheet = $obj_PHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象

        //$obj_PHPExcel->createSheet();//可以循环创建多个sheet,在建立多个sheet时使用
        //$obj_PHPExcel->setActiveSheetIndex(0);//设置为活动sheet 从0开始
        //$obj_Sheet = $obj_PHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象

        $obj_Sheet->setTitle('s1');//设置当前活动Sheet名称

        /* 填充数据 */
        $result = array();
        foreach ($data as $index => $value) {
            foreach ($value as $k => $v) {
                if (!array_key_exists($k, $header)) {
                    unset($value[$k]);
                }
            }
            $row = array_merge($header, $value);
            $result[] = $row;
        }


        // 1、设置头
        foreach ($header as $field => $name) {
            $obj_Sheet->setCellValue($this->setContentRowIndex(), $name);
        }
        $this->initContentNum();


        //2、填充内容
        foreach ($result as $index => $value) {
            foreach ($value as $k => $v) {
                $obj_Sheet->setCellValue($this->setContentRowIndex(), $v);
            }
            $this->initContentNum();
        }
        //1.逐个单元格进行填充
//        $obj_Sheet->setCellValue("A1", "姓名")->setCellValue("B1", "分数");//填充数据
//        $obj_Sheet->setCellValue("A2", "张三")->setCellValue("B2", "50");//填充数据

        //$obj_PHPExcel->getActiveSheet()->mergeCells('A1:F1');//合并单元格A1:F1(起始坐标，结束坐标)
        //$obj_PHPExcel->getActiveSheet()->unmergeCells('A1:F1');// 拆分单元格

        $obj_Sheet->getDefaultStyle()->getAlignment()//设置居中显示
        ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)//垂直居中
        ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中

        //2.传入数组的方式填充
        //$obj_Sheet->fromArray([
        //    ['标题1','标题2'], //第一个数组为第一行，数组的第一个元素为第一列
        //    ['1','2'],
        //    ['3','4'],
        //    ['5','6'],
        //]);

//        $result = [
//            ['id' => 'ID号', 'name' => '用户', 'pwd' => '密码', 'addr' => '地址'],
//            ['id' => '2w5s2525dw88ee8wq87', 'name' => 'test01', 'pwd' => '123456', 'addr' => '广州'],
//            ['id' => 'd15as6ds1d6as11das6', 'name' => 'test02', 'pwd' => '123456', 'addr' => '广州'],
//        ];
//        $obj_Sheet->fromArray($result);//填充数组

        $obj_Writer = PHPExcel_IOFactory::createWriter($obj_PHPExcel, 'Excel2007');//创建工厂对象

        //操作1 保存文件
//        $obj_Writer->save('demo.xlsx');//执行保存文件

        //操作2 输出浏览器
        $this->browser_export('demo', false);
        $obj_Writer->save('php://output');
    }


    /**
     * 初始化行数
     */
    protected function initContentNum()
    {
        self::$row_num++;
        self::$index = 0;
    }

    /**
     * 设置内容行数
     * @return string
     */
    protected function setContentRowIndex()
    {
        $rowName = $this->getRowName(self::$index) . "" . (self::$row_num);
        self::$index++;
        return $rowName;
    }

    /**
     * 获取行dom
     * 例：A,B,C.....,AA,AB,AC....,BA,BC...
     * @param $index
     * @return mixed|string
     */
    private function getRowName($index)
    {
        $row_total = count(self::ROW);
        static $_index = 0;
        $row = "";
        if ($index >= $row_total) {
            $row = self::ROW[$_index % $row_total];
            $_index++;
            $i = $index - $row_total;
            if ($i >= $row_total) {
                $row = $this->getRowName($i);
            } else {
                $row .= self::ROW[$i];
            }
        } else {
            $row .= self::ROW[$index];
        }
        $_index = 0;
        return $row;
    }

    /**
     * 输出到浏览器 判断文件类型
     * @param $filename
     * @param string $type
     */
    public function browser_export($filename, $type = 'Excel5')
    {
        if ($type == 'Excel5') {
            $ext = '.xls';
            header('Content-Type: application/vnd.ms-excel');//输出excel03文件
        } else {
            $ext = '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//输出excel07文件
        }
        header('Content-Disposition: attachment;filename="' . $filename . $ext . '"');//输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }
}